package com.txzt.gaea.business.project.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.fastjson.JSONObject;
import com.txzt.base.core.RtnInfo;
import com.txzt.gaea.aspect.annotation.SysDataPermission;
import com.txzt.gaea.base.service.DictInfoService;
import com.txzt.gaea.business.project.domain.model.ProjectFund;
import com.txzt.gaea.business.project.domain.model.ProjectFundExpenseRequest;
import com.txzt.gaea.business.project.domain.vo.*;
import com.txzt.gaea.business.project.service.IProjectFERService;
import com.txzt.gaea.business.project.service.IProjectFundService;
import com.txzt.gaea.business.util.TranslateUtil;
import com.txzt.gaea.common.annotation.EntityCommonValue;
import com.txzt.gaea.common.base.controller.BaseController;
import com.txzt.gaea.common.core.base.context.AdminUserContext;
import com.txzt.gaea.common.enums.ProjectFERFundType;
import com.txzt.gaea.common.enums.ProjectFERStatus;
import com.txzt.gaea.vo.AdminUserVO;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.beetl.sql.core.engine.PageQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/project/FER")
public class ProjectFERController extends BaseController {

    @Value("${template.devicePath}")
    String devicePath;

    @Autowired
    private IProjectFundService projectFundService;
    @Autowired
    private IProjectFERService iProjectFERService;

    /**
     * 分页查询
     * @param pageNumber
     * @param pageSize
     * @param projectFund
     * @return
     */
    @SysDataPermission
    @GetMapping(value = "/list")
    public RtnInfo<?> pageList(@RequestParam(defaultValue = "1") Integer pageNumber,
                               @RequestParam(defaultValue = "10") Integer pageSize, ProjectFundExpenseRequest projectFund) {
        PageQuery<ProjectFundExpenseRequest> pageList = iProjectFERService.pageQuery(new PageQuery<>(pageNumber, pageSize, projectFund));
        pageList.getList().forEach(item -> {
            //翻译创建人
//            item.setExpenseUserName(TranslateUtil.getUserName(item.getExpenseBy()));
            //部门
            item.setDeptName(TranslateUtil.getDepartmentName(item.getOrgId()));
            //经费类型
            item.setFundTypeName(ProjectFERFundType.getInfoByCode(item.getFundType()));
            //状态
            item.setStatusName(ProjectFERStatus.getInfoByCode(item.getStatus()));
            if (null != item.getProjectList()){
                item.setProjectShowList(Arrays.asList(item.getProjectList().split(",")));
            }
        });
        return RtnInfo.success(pageList);
    }

    /**
     * 查询待审核数量(项目过程经费支出申请)
     * @param projectFund
     * @return
     */
    @SysDataPermission
    @GetMapping(value = "/listCheckCount")
    public RtnInfo<?> listCheckCount(ProjectFundExpenseRequest projectFund) {
        Long count = iProjectFERService.listCheckCount(projectFund);
        return RtnInfo.success(count);
    }

    /**
     * 分页查询(流程查询)
     * @param pageNumber
     * @param pageSize
     * @param projectFund
     * @return
     */
    @GetMapping(value = "/listCheck")
    public RtnInfo<?> listCheck(@RequestParam(defaultValue = "1") Integer pageNumber,
                               @RequestParam(defaultValue = "10") Integer pageSize, ProjectFund projectFund) {
        AdminUserVO adminUserVO = AdminUserContext.getUser();
        List<Long> roleIds = Arrays.stream(adminUserVO.getRoleid().split(",")).mapToLong(Long::valueOf).boxed().collect(Collectors.toList());
        projectFund.setRoleIds(roleIds);
        PageQuery<ProjectFund> pageList = projectFundService.pageQuery(new PageQuery<>(pageNumber, pageSize, projectFund));
        pageList.getList().forEach(item -> {
            //翻译创建人
            item.setCreateName(TranslateUtil.getUserName(item.getCreateBy()));
        });
        return RtnInfo.success(pageList);
    }

    @GetMapping(value = "/stats/list")
    public RtnInfo<?> statsList(@RequestParam(defaultValue = "1") Integer pageNumber,
                               @RequestParam(defaultValue = "10") Integer pageSize, ProjectFERStats projectFERStats) {
        PageQuery<ProjectFERStats> pageQuery = new PageQuery<>(pageNumber, pageSize, projectFERStats);
        PageQuery<ProjectFERStats> pageList = iProjectFERService.ProjectFERStatsList(pageQuery);
        pageList.getList().forEach(item -> {
            //翻译创建人
            item.setCreateName(TranslateUtil.getUserName(item.getCreateBy()));
            item.setProjectLeaderName(TranslateUtil.getUserName(item.getProjectLeader()));
            //部门
            item.setDeptName(TranslateUtil.getDepartmentName(item.getDeptId()));
            item.setDepartName(TranslateUtil.getDepartmentName(item.getDepartId()));
        });
        return RtnInfo.success(pageList);
    }

    @GetMapping(value = "/stats/detail")
    public RtnInfo<?> statsDetailList(@RequestParam Long projectId) {
        ProjectFERStatsDetail queryParam = new ProjectFERStatsDetail();
        queryParam.setProjectId(projectId);
        return RtnInfo.success(iProjectFERService.getProjectFERStatsDetail(queryParam));
    }

    @GetMapping(value = "/ledger/list")
    public RtnInfo<?> ledgerList(@RequestParam(defaultValue = "1") Integer pageNumber,
                                @RequestParam(defaultValue = "10") Integer pageSize, ProjectFERLedger projectFERLedger) {
        PageQuery<ProjectFERLedger> pageQuery = new PageQuery<>(pageNumber, pageSize, projectFERLedger);
        PageQuery<ProjectFERLedger> pageList = iProjectFERService.ProjectFERLedgerList(pageQuery);
        return RtnInfo.success(pageList);
    }

    @GetMapping(value = "/ledger/detail")
    public RtnInfo<?> ledgerDetailList(@RequestParam String ledgerAccount, @RequestParam String annual) {
        ProjectFERLedgerDetail queryParam = new ProjectFERLedgerDetail();
        queryParam.setLedgerAccount(ledgerAccount);
        queryParam.setAnnual(annual);
        return RtnInfo.success(iProjectFERService.getProjectFERLedgerDetail(queryParam));
    }

    /**
     * id查询
     * @param id
     * @return
     */
    @GetMapping(value = "/queryById")
    public RtnInfo<?> queryById(@RequestParam Long id) {
        ProjectFundExpenseRequest projectFund = iProjectFERService.selectById(id);
        if (null != projectFund) {
            return RtnInfo.success(projectFund);
        } else {
            return RtnInfo.SERVER_ERROR;
        }
    }

    /**
     * 保存
     * @param projectFundExpenseRequest
     * @return
     */
    @PostMapping(value = "/save")
    @EntityCommonValue(operation = "CREATE")
    public RtnInfo<?> save(@RequestBody ProjectFundExpenseRequest projectFundExpenseRequest) {
        int count;
        if (null == projectFundExpenseRequest.getId()){
            projectFundExpenseRequest.setStatus(ProjectFERStatus.INIT.getCode());
            count = iProjectFERService.savePFER(projectFundExpenseRequest);
        }else {
            count = iProjectFERService.updatePFEROnlySave(projectFundExpenseRequest);
        }
        if (count > 0) {
            return RtnInfo.SUCCESS;
        } else {
            return RtnInfo.SERVER_ERROR;
        }
    }

    /**
     * 提交
     * @param projectFundExpenseRequest
     * @return
     */
    @PostMapping(value = "/submit")
    @EntityCommonValue(operation = "CREATE")
    public RtnInfo<?> submit(@RequestBody ProjectFundExpenseRequest projectFundExpenseRequest) {
        int count;
        if (null == projectFundExpenseRequest.getId()){
            projectFundExpenseRequest.setStatus(ProjectFERStatus.CHECK.getCode());
            count = iProjectFERService.savePFER(projectFundExpenseRequest);
        }else {
            count = iProjectFERService.updatePFER(projectFundExpenseRequest);
        }
        if (count > 0) {
            return RtnInfo.SUCCESS;
        } else {
            return RtnInfo.SERVER_ERROR;
        }
    }

    /**
     * 仅修改
     * @param projectFundExpenseRequest
     * @return
     */
    @PostMapping(value = "/update")
    public RtnInfo<?> update(@RequestBody ProjectFundExpenseRequest projectFundExpenseRequest) {
        int count = iProjectFERService.updatePFER(projectFundExpenseRequest);
        if (count > 0) {
            return RtnInfo.SUCCESS;
        } else {
            return RtnInfo.SERVER_ERROR;
        }
    }

    /**
     * 撤回
     * @param projectFundExpenseRequest
     * @return
     */
    @PostMapping(value = "/back")
    public RtnInfo<?> back(@RequestBody ProjectFundExpenseRequest projectFundExpenseRequest) {
        int count = iProjectFERService.revocationPFER(projectFundExpenseRequest);
        if (count == 99) {
            return RtnInfo.error(99,"申请状态不对，不可撤回");
        } else if(count == 1){
            return RtnInfo.SUCCESS;
        }else {
            return RtnInfo.SERVER_ERROR;
        }
    }

    /**
     * 填写支出
     * @param projectFundExpenseRequest
     * @return
     */
    @PostMapping(value = "/expense")
    public RtnInfo<?> expense(@RequestBody ProjectFundExpenseRequest projectFundExpenseRequest) {
        int count = iProjectFERService.expenseInfo(projectFundExpenseRequest);
        if(count == 1){
            return RtnInfo.SUCCESS;
        }else {
            return RtnInfo.SERVER_ERROR;
        }
    }

    /**
     * 删除
     * @param id
     * @return
     */
    @DeleteMapping(value = "/delete")
    @EntityCommonValue(operation = "UPDATE")
    public RtnInfo<?> delete(@RequestParam Long id) {
        ProjectFundExpenseRequest projectFundExpenseRequest = iProjectFERService.selectById(id);
        if (ProjectFERStatus.INIT.getCode().equals(projectFundExpenseRequest.getStatus())) {
            iProjectFERService.deleteInfo(id);
            return RtnInfo.SUCCESS;
        }else {
            return RtnInfo.error(99,"申请状态错误，不可删除");
        }
    }

    @Autowired
    private DictInfoService dictInfoService;

    /**
     * 导出
     */
    @GetMapping(value = "/device/export")
    public void deviceExportExcel(HttpServletResponse response, @RequestParam Long id) throws IOException {
        ProjectFundExpenseRequest projectFundExpenseRequest = iProjectFERService.selectById(id);
        String deptName = TranslateUtil.getDepartmentName(projectFundExpenseRequest.getDeptId());
        String ledgerAccountNames = "";
        List<HashMap> projectList = JSONObject.parseArray(projectFundExpenseRequest.getProjectDetail(), HashMap.class);
        for (HashMap map : projectList){
            if (null != map.get("ledgerAccount")){
                ledgerAccountNames = ledgerAccountNames + dictInfoService.dictTranslate("ledger_account",(String)map.get("ledgerAccount")) + ",";
            }
        }
        BigDecimal totalPrice = BigDecimal.ZERO;
        String procs = "";
        List<ProjectFERDeviceDetail> otherList = JSONObject.parseArray(projectFundExpenseRequest.getOtherDetail(), ProjectFERDeviceDetail.class);
        List<ProjectFERDeviceDetail> voList = new ArrayList<>();
        for (ProjectFERDeviceDetail map : otherList){
            if (null != map.getQty() && null != map.getPrice()){
                map.setTotal(map.getQty().multiply(map.getPrice()));
                totalPrice = totalPrice.add(map.getTotal());
            }
            if (null != map.getProc()){
                map.setProcName(dictInfoService.dictTranslate("procurement_method",map.getProc()));
                procs = procs + dictInfoService.dictTranslate("procurement_method",map.getProc()) + ",";
            }
            voList.add(map);
        }
        String templateFileName = devicePath;
//        String fileName = devicePath.split(".xlsx")[0] + System.currentTimeMillis() + ".xlsx";
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("零星设备、物资、服务、采购审批单数据导出", "UTF-8").replaceAll("\\+", "%20");
        // 方案1
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            // 如果数据量大 list不是最后一行 参照下一个
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(voList, fillConfig, writeSheet);
            Map<String, Object> map = MapUtils.newHashMap();
            String pattern = "yyyy-MM-dd HH:mm:ss";
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat(pattern);
            map.put("expenseTime", simpleDateFormat.format(projectFundExpenseRequest.getExpenseTime()));
            map.put("deptName", deptName);
            map.put("ledgerAccountNames", ledgerAccountNames.substring(0,ledgerAccountNames.length() - 1));
            map.put("executableBudget", projectFundExpenseRequest.getExecutableBudget());
            map.put("yearBudget", projectFundExpenseRequest.getYearBudget());
            map.put("totalPrice", totalPrice);
            map.put("remark", projectFundExpenseRequest.getRemark());
            map.put("procs", procs.substring(0,procs.length() - 1));
            excelWriter.fill(map, writeSheet);
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
        }catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = new HashMap<>();
            map.put("code", 500);
            map.put("msg", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSONObject.toJSONString(map));
        }
    }

    @Value("${template.meetingAPath}")
    String meetingAPath;

    @GetMapping(value = "/meeting/export/a")
    public void meetingExportExcel(HttpServletResponse response, @RequestParam Long id) throws IOException {
        ProjectFundExpenseRequest projectFundExpenseRequest = iProjectFERService.selectById(id);
        String ledgerAccountNames = "";
        String projectNames = "";
        List<HashMap> projectList = JSONObject.parseArray(projectFundExpenseRequest.getProjectDetail(), HashMap.class);
        for (HashMap map : projectList){
            if (null != map.get("ledgerAccount")){
                ledgerAccountNames = ledgerAccountNames + dictInfoService.dictTranslate("ledger_account",(String)map.get("ledgerAccount")) + ",";
            }
            if (null != map.get("projectName")){
                projectNames = projectNames + map.get("projectName") + ",";
            }
        }
        ProjectFERMeetingADetail otherDetail = JSONObject.parseObject(projectFundExpenseRequest.getOtherDetailB(), ProjectFERMeetingADetail.class);
        if (!StringUtils.isEmpty(ledgerAccountNames)){
            otherDetail.setLedgerAccountNames(ledgerAccountNames.substring(0,ledgerAccountNames.length() - 1));
        }
        otherDetail.setProjectNames(projectNames.substring(0,projectNames.length() - 1));
        if (null != otherDetail.getDateRange() && 0 < otherDetail.getDateRange().length){
            otherDetail.setDateRangeA(otherDetail.getDateRange()[0]);
            otherDetail.setDateRangeB(otherDetail.getDateRange()[1]);
        }
        otherDetail.setVenueName(dictInfoService.dictTranslate("venue_place",otherDetail.getVenue()));
        otherDetail.setMechanismNatureName(dictInfoService.dictTranslate("mechanism_nature",otherDetail.getMechanismNature()));
        otherDetail.setMechanismNameTL(dictInfoService.dictTranslate("mechanism_name",otherDetail.getMechanismName()));
        otherDetail.setTotalRemark(projectFundExpenseRequest.getRemark());

        String templateFileName = meetingAPath;
//        String fileName = devicePath.split(".xlsx")[0] + System.currentTimeMillis() + ".xlsx";
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("会议费数据导出", "UTF-8").replaceAll("\\+", "%20");
        // 方案1
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            // 如果数据量大 list不是最后一行 参照下一个
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(otherDetail, writeSheet);
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
        }catch (Exception e) {
            // 重置response
//            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = new HashMap<>();
            map.put("code", 500);
            map.put("msg", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSONObject.toJSONString(map));
        }
    }
    @Value("${template.tempPath}")
    String tempPath;
    @Value("${template.meetingBPath}")
    String meetingBPath;

    @GetMapping(value = "/meeting/export/b")
    public void meetingBExportExcel(HttpServletResponse response, @RequestParam Long id) throws IOException {
        ProjectFundExpenseRequest projectFundExpenseRequest = iProjectFERService.selectById(id);
        List<HashMap> projectList = JSONObject.parseArray(projectFundExpenseRequest.getProjectDetail(), HashMap.class);
        ProjectFERMeetingADetail otherDetail = JSONObject.parseObject(projectFundExpenseRequest.getOtherDetailB(), ProjectFERMeetingADetail.class);
//        if (null != otherDetail.getDateRange() && 0 < otherDetail.getDateRange().length){
//            otherDetail.setDateRangeA(otherDetail.getDateRange()[0]);
//            otherDetail.setDateRangeB(otherDetail.getDateRange()[1]);
//        }
        if (null != otherDetail.getZxfTime() && 0 < otherDetail.getZxfTime().length){
            otherDetail.setZxfTimeA(otherDetail.getZxfTime()[0]);
            otherDetail.setZxfTimeB(otherDetail.getZxfTime()[1]);
        }
        List<ProjectFERMeetingBDetail> otherList = JSONObject.parseArray(projectFundExpenseRequest.getOtherDetail(), ProjectFERMeetingBDetail.class);
        for (int i=0;i<otherList.size();i++){
            if(otherList.get(i).isExpert()){
                otherList.get(i).setSort(i+1);
            }else {
                otherList.remove(i);
            }
        }
        String templateFileName = meetingBPath;
//        String fileName = devicePath.split(".xlsx")[0] + System.currentTimeMillis() + ".xlsx";
        String tempFileName = tempPath + System.currentTimeMillis() + ".xlsx";
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("咨询费发放表-会议数据导出", "UTF-8").replaceAll("\\+", "%20");
        // 方案1
        try (ExcelWriter excelWriter = EasyExcel.write(tempFileName).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            // 如果数据量大 list不是最后一行 参照下一个
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(otherList, fillConfig, writeSheet);
            excelWriter.fill(otherDetail, writeSheet);
            excelWriter.finish();
            FileInputStream fis = new FileInputStream(tempFileName);
            //1.创建一个工作簿
            Workbook workbook = new XSSFWorkbook(fis);
            //获取工作表
            Sheet sheet1 = workbook.getSheet("Sheet1");
            Font font = workbook.createFont();
            //通过行下标和列下标返回cell对象，getRow行标,getCell列标
            Cell cell = sheet1.getRow(3).getCell(7);
            Cell cell1 = sheet1.getRow(3).getCell(1);
            RichTextString richTextString;
            RichTextString richTextString1;
            // 设置字体名称
            font.setFontName("Wingdings 2");
            if (projectList.get(0).get("planType").equals("technical foundation")
                    || projectList.get(0).get("planType").equals("after_research")
                    || projectList.get(0).get("planType").equals("system_pre-research")
                    || projectList.get(0).get("planType").equals("Comprehensive_Study")) {
                richTextString = new XSSFRichTextString("\u0052 科研经费 \u25A1 其他经费");
                richTextString.applyFont(0, 1, font);
            } else {
                richTextString = new XSSFRichTextString("\u25A1 科研经费 \u0052 其他经费");
                richTextString.applyFont(7, 8, font);
            }
            richTextString1 = new XSSFRichTextString("\u0052 会议 \u25A1 现场访谈、勘察 \u25A1 通讯");
            richTextString1.applyFont(0, 1, font);
            //设置单元格的值
            cell.setCellValue(richTextString);
            cell1.setCellValue(richTextString1);
            FileOutputStream fos = new FileOutputStream(tempFileName);
            //写入
            workbook.write(fos);
            workbook.close();
            fis.close();
            ExcelWriter excelWriter1 = EasyExcel.write(response.getOutputStream()).withTemplate(tempFileName).build();
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter1.finish();
        }catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = new HashMap<>();
            map.put("code", 500);
            map.put("msg", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSONObject.toJSONString(map));
        }
    }

    @Value("${template.meetingBPath}")
    String meetingCPath;

    @GetMapping(value = "/meeting/export/c")
    public void meetingCExportExcel(HttpServletResponse response, @RequestParam Long id) throws IOException {
        ProjectFundExpenseRequest projectFundExpenseRequest = iProjectFERService.selectById(id);
        List<HashMap> projectList = JSONObject.parseArray(projectFundExpenseRequest.getProjectDetail(), HashMap.class);
        ProjectFERMeetingADetail otherDetail = JSONObject.parseObject(projectFundExpenseRequest.getOtherDetailB(), ProjectFERMeetingADetail.class);
//        otherDetail.setDateRangeA(otherDetail.getDateRange()[0]);
//        otherDetail.setDateRangeB(otherDetail.getDateRange()[1]);
        if (null != otherDetail.getZxfTime() && 0 < otherDetail.getZxfTime().length){
            otherDetail.setZxfTimeA(otherDetail.getZxfTime()[0]);
            otherDetail.setZxfTimeB(otherDetail.getZxfTime()[1]);
        }
        List<ProjectFERMeetingBDetail> otherList = JSONObject.parseArray(projectFundExpenseRequest.getOtherDetail(), ProjectFERMeetingBDetail.class);
        for (int i=0;i<otherList.size();i++){
            if(otherList.get(i).isExpert()){
                otherList.get(i).setSort(i+1);
            }else {
                otherList.remove(i);
            }
        }
        String templateFileName = meetingCPath;
//        String fileName = devicePath.split(".xlsx")[0] + System.currentTimeMillis() + ".xlsx";
        String tempFileName = tempPath + System.currentTimeMillis() + ".xlsx";
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("咨询费发放表-勘察数据导出", "UTF-8").replaceAll("\\+", "%20");
        // 方案1
        try (ExcelWriter excelWriter = EasyExcel.write(tempFileName).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            // 如果数据量大 list不是最后一行 参照下一个
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(otherList, fillConfig, writeSheet);
            excelWriter.fill(otherDetail, writeSheet);
            excelWriter.finish();
            FileInputStream fis = new FileInputStream(tempFileName);
            //1.创建一个工作簿
            Workbook workbook = new XSSFWorkbook(fis);
            //获取工作表
            Sheet sheet1 = workbook.getSheet("Sheet1");
            Font font = workbook.createFont();
            //通过行下标和列下标返回cell对象，getRow行标,getCell列标
            Cell cell = sheet1.getRow(3).getCell(7);
            Cell cell1 = sheet1.getRow(3).getCell(1);
            RichTextString richTextString;
            RichTextString richTextString1;
            // 设置字体名称
            font.setFontName("Wingdings 2");
            if (projectList.get(0).get("planType").equals("technical foundation")
                    || projectList.get(0).get("planType").equals("after_research")
                    || projectList.get(0).get("planType").equals("system_pre-research")
                    || projectList.get(0).get("planType").equals("Comprehensive_Study")) {
                richTextString = new XSSFRichTextString("\u0052 科研经费 \u25A1 其他经费");
                richTextString.applyFont(0, 1, font);
            } else {
                richTextString = new XSSFRichTextString("\u25A1 科研经费 \u0052 其他经费");
                richTextString.applyFont(7, 8, font);
            }
            richTextString1 = new XSSFRichTextString("\u25A1 会议 \u0052 现场访谈、勘察 \u25A1 通讯");
            richTextString1.applyFont(5, 6, font);
            //设置单元格的值
            cell.setCellValue(richTextString);
            cell1.setCellValue(richTextString1);
            FileOutputStream fos = new FileOutputStream(tempFileName);
            //写入
            workbook.write(fos);
            workbook.close();
            fis.close();
            ExcelWriter excelWriter1 = EasyExcel.write(response.getOutputStream()).withTemplate(tempFileName).build();
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter1.finish();
        }catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = new HashMap<>();
            map.put("code", 500);
            map.put("msg", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSONObject.toJSONString(map));
        }
    }

    @Value("${template.meetingDPath}")
    String meetingDPath;

    @GetMapping(value = "/meeting/export/d")
    public void meetingDExportExcel(HttpServletResponse response, @RequestParam Long id) throws IOException {
        ProjectFundExpenseRequest projectFundExpenseRequest = iProjectFERService.selectById(id);
        List<ProjectFERMeetingBDetail> otherList = JSONObject.parseArray(projectFundExpenseRequest.getOtherDetail(), ProjectFERMeetingBDetail.class);
        for (int i=0;i<otherList.size();i++){
            otherList.get(i).setSort(i+1);
        }
        String templateFileName = meetingDPath;
//        String fileName = devicePath.split(".xlsx")[0] + System.currentTimeMillis() + ".xlsx";
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("入会签到表导出", "UTF-8").replaceAll("\\+", "%20");
        // 方案1
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            // 如果数据量大 list不是最后一行 参照下一个
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(otherList, fillConfig, writeSheet);
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
        }catch (Exception e) {
            // 重置response
//            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = new HashMap<>();
            map.put("code", 500);
            map.put("msg", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSONObject.toJSONString(map));
        }
    }

    @Value("${template.TXFPath}")
    String TXFPath;

    @GetMapping(value = "/txf/export")
    public void txfExportExcel(HttpServletResponse response, @RequestParam Long id) throws IOException {
        ProjectFundExpenseRequest projectFundExpenseRequest = iProjectFERService.selectById(id);
//        List<HashMap> projectList = JSONObject.parseArray(projectFundExpenseRequest.getProjectDetail(), HashMap.class);
        List<ProjectFERMeetingBDetail> otherList = JSONObject.parseArray(projectFundExpenseRequest.getOtherDetail(), ProjectFERMeetingBDetail.class);
        for (int i=0;i<otherList.size();i++){
            otherList.get(i).setSort(i+1);
        }
        HashMap otherDetail = JSONObject.parseObject(projectFundExpenseRequest.getOtherDetailB(), HashMap.class);
        String templateFileName = TXFPath;
//        String fileName = devicePath.split(".xlsx")[0] + System.currentTimeMillis() + ".xlsx";
        String tempFileName = tempPath + System.currentTimeMillis() + ".xlsx";
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("咨询费发放表-通讯费数据导出", "UTF-8").replaceAll("\\+", "%20");
        // 方案1
        try (ExcelWriter excelWriter = EasyExcel.write(tempFileName).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            // 如果数据量大 list不是最后一行 参照下一个
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(otherList, fillConfig, writeSheet);
            excelWriter.fill(otherDetail, writeSheet);
            excelWriter.finish();
            FileInputStream fis = new FileInputStream(tempFileName);
            //1.创建一个工作簿
            Workbook workbook = new XSSFWorkbook(fis);
            //获取工作表
            Sheet sheet1 = workbook.getSheet("Sheet1");
            Font font = workbook.createFont();
            //通过行下标和列下标返回cell对象，getRow行标,getCell列标
            Cell cell = sheet1.getRow(3).getCell(7);
            RichTextString richTextString;
            // 设置字体名称
            font.setFontName("Wingdings 2");
            if (otherDetail.get("fundSource").equals("sr")) {
                richTextString = new XSSFRichTextString("\u0052 科研经费 \u25A1 其他经费");
                richTextString.applyFont(0, 1, font);
            } else {
                richTextString = new XSSFRichTextString("\u25A1 科研经费 \u0052 其他经费");
                richTextString.applyFont(7, 8, font);
            }
            //设置单元格的值
            cell.setCellValue(richTextString);
            FileOutputStream fos = new FileOutputStream(tempFileName);
            //写入
            workbook.write(fos);
            workbook.close();
            fis.close();
            ExcelWriter excelWriter1 = EasyExcel.write(response.getOutputStream()).withTemplate(tempFileName).build();
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter1.finish();
        }catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = new HashMap<>();
            map.put("code", 500);
            map.put("msg", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSONObject.toJSONString(map));
        }
    }

    @Value("${template.JJFAPath}")
    String JJFAPath;

    @GetMapping(value = "/jjf/export/a")
    public void jjfAExportExcel(HttpServletResponse response, @RequestParam Long id) throws IOException {
        ProjectFundExpenseRequest projectFundExpenseRequest = iProjectFERService.selectById(id);
        List<ProjectFERJJFDetail> projectList = JSONObject.parseArray(projectFundExpenseRequest.getProjectDetail(), ProjectFERJJFDetail.class);

        List<ProjectFERMeetingBDetail> otherDetail = JSONObject.parseArray(projectFundExpenseRequest.getOtherDetail(), ProjectFERMeetingBDetail.class);
        String names = "";
        for (ProjectFERMeetingBDetail map : otherDetail){
            names = names + map.getName() + ",";
        }
        projectList.get(0).setProjectPerson(names.substring(0,names.length() - 1));
        String templateFileName = JJFAPath;
//        String fileName = devicePath.split(".xlsx")[0] + System.currentTimeMillis() + ".xlsx";
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("科研岗位绩效津贴发放申请表导出", "UTF-8").replaceAll("\\+", "%20");
        // 方案1
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            // 如果数据量大 list不是最后一行 参照下一个
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(projectList.get(0), writeSheet);
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
        }catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = new HashMap<>();
            map.put("code", 500);
            map.put("msg", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSONObject.toJSONString(map));
        }
    }

    @Value("${template.JJFBPath}")
    String JJFBPath;

    @GetMapping(value = "/jjf/export/b")
    public void jjfBExportExcel(HttpServletResponse response, @RequestParam Long id) throws IOException {
        ProjectFundExpenseRequest projectFundExpenseRequest = iProjectFERService.selectById(id);
        List<ProjectFERMeetingBDetail> otherDetail = JSONObject.parseArray(projectFundExpenseRequest.getOtherDetail(), ProjectFERMeetingBDetail.class);
        BigDecimal totalBonus = BigDecimal.ZERO;
        for (ProjectFERMeetingBDetail map : otherDetail){
            totalBonus = totalBonus.add(map.getBonus());
        }
        String templateFileName = JJFBPath;
//        String fileName = devicePath.split(".xlsx")[0] + System.currentTimeMillis() + ".xlsx";
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("科研岗位绩效津贴发放申请表导出", "UTF-8").replaceAll("\\+", "%20");
        // 方案1
        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            // 如果数据量大 list不是最后一行 参照下一个
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(otherDetail, fillConfig,writeSheet);
            Map<String,Object> map = new HashMap<>();
            map.put("totalBonus",totalBonus);
            excelWriter.fill(map, writeSheet);
            // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            excelWriter.finish();
        }catch (Exception e) {
            // 重置response
//            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, Object> map = new HashMap<>();
            map.put("code", 500);
            map.put("msg", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSONObject.toJSONString(map));
        }
    }
}